package com.dao;

import com.bean.Student;
import com.bean.DB;

import java.sql.*;
import java.util.ArrayList;

public class studentDAO {
    private String sql="";
    private PreparedStatement pstmt;
    private ResultSet rs;
    private ArrayList<Student> stu_info =
            new ArrayList<Student>();
    private Student student;
    private Connection conn=null;
    private Connection getConn(){
        try {
            if ((conn==null)||conn.isClosed()){
                DB db=new DB();
                conn=db.getConn();
            }
        }catch (SQLException jzt){
            jzt.printStackTrace();
        }
        return conn;
    }
    public ArrayList<Student> getAll(){
        sql="select * from students";
        try{
            pstmt=getConn().prepareStatement(sql);
            rs=pstmt.executeQuery();
            stu_info = getByRs(rs);
            System.out.println("books"+ stu_info);
        }catch (SQLException jzt){
            jzt.printStackTrace();
        }finally {
            close();
        }
        return stu_info;
    }

    private ArrayList<Student> getByRs(ResultSet rs) {

        try{
            if (rs==null&&!rs.next())
                return null;
            stu_info.clear();

                while (rs.next()){
                    student =new Student();
                    student.setId(rs.getInt("id"));
                    student.setName(rs.getString("student"));
                    student.setGender(rs.getString("gender"));
                    student.setAge(rs.getInt("age"));
                    student.setPhone(rs.getString("phone"));
                    student.setMajor(rs.getString("major"));
                    student.setClasses(rs.getString("classes"));
                    stu_info.add(student);


            }

            }catch (SQLException jzt){
            jzt.printStackTrace();
        }
        return stu_info;
    }
    public void close(){
        try{
            if (rs!=null)
                rs.close();
            if (pstmt!=null)
                pstmt.close();
            if (conn!=null)
                conn.close();
        }catch (Exception jzt){
            jzt.printStackTrace();
        }finally {
            rs=null;
            pstmt=null;
            conn=null;
        }
    }
public ArrayList<Student> getByName(String name){
    getConn();
    if (name==null)
        name="";
    try{
        CallableStatement cs=conn.prepareCall("{call NewProc(?)}");
        cs.setString(1,name);
        rs=cs.executeQuery();
        stu_info = getByRs(rs);
    }catch (SQLException e){
        e.printStackTrace();
    }finally {
        close();
    }return stu_info;


}
public Student getById(int id){
        getConn();
        sql="select * from students where id=?";
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            rs=pstmt.executeQuery();
            stu_info = getByRs(rs);
            System.out.println("id"+ stu_info);
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            close();
        }return stu_info.get(0);
}
public boolean updateStu(Student student){
        sql="update students set student=?,gender=?,age=?,phone=?,major=?where id=?";
        int result=0;
        try {
            pstmt=getConn().prepareStatement(sql);
            pstmt.setString(1,student.getName());
            pstmt.setString(2, student.getGender());
            pstmt.setInt(3,student.getAge());
            pstmt.setString(4,student.getPhone());
            pstmt.setString(5,student.getMajor());
            pstmt.setInt(6,student.getId());
            System.out.println("pstm"+pstmt);
            result=pstmt.executeUpdate();
            System.out.println("result"+result);


        }catch (Exception e){
            e.printStackTrace();
        }finally {
            close();
        }if (result>0)
            return true;
        else
            return false;
}

public boolean addStu(Student student){
        sql="insert into students(student,gender,age,phone,major,classes) values(?,?,?,?,?,?)";
        int result=0;
    try {
        pstmt=getConn().prepareStatement(sql);
        pstmt.setString(1,student.getName());
        pstmt.setString(2, student.getGender());
        pstmt.setInt(3,student.getAge());
        pstmt.setString(4,student.getPhone());
        pstmt.setString(5,student.getMajor());
        pstmt.setString(6,student.getClasses());
        System.out.println("add_rs"+pstmt);
        result = pstmt.executeUpdate();
    } catch(SQLException e) {
        e.printStackTrace();
    }finally {
        close();
    }
    if(result > 0)
        return true;
    else
        return false;

}
public boolean delete(int id){
        sql="delete from students where id=?";
        int result=0;
        try{
            pstmt=getConn().prepareStatement(sql);
            pstmt.setInt(1,id);
            result=pstmt.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            close();
        }if (result>0)
            return true;
        else
            return false;
}


}

